import os
import time
import datetime
import schedule
import pymysql
from openpyxl import Workbook
from openpyxl.styles import PatternFill
from openpyxl.styles import Font  # 添加这行导入
from openpyxl.utils import get_column_letter  # 添加这行导入
from DBUtils.PooledDB import PooledDB
from conf import DbConfig, TenantId
from database import Mysql


# 配置地市、区县和企业名称映射（根据实际情况修改）
# 由于conf.py中未找到这些配置，暂在此处定义，您可以根据需要移至conf.py
# company_info = {
#     # 格式: 企业ID: [地市, 区县, 企业名称]
#     '59': ['杭州市', '西湖区', '杭州示例企业1'],
#     '101': ['宁波市', '鄞州区', '宁波示例企业2'],
#     # 更多企业信息...
# }

# 数据库连接池
class MysqlPool:
    __pool = None

    @staticmethod
    def get_conn(tenant_id):
        db_conn = Mysql(DbConfig, "ecloud_chain_" + str(tenant_id))
        return db_conn

    @staticmethod
    def get_tenant_conn():
        return Mysql(DbConfig, "ecloud_chain_0")


# 获取最新时间的通用函数
def get_latest_time(conn, sql, date_field='create_time'):
    try:
        result = conn.get_one(sql)
        if result and result[date_field]:
            # 检查是否为秒级时间戳(数字类型)
            timestamp = result[date_field]
            if isinstance(timestamp, (int, float)):
                # 转换为具体日期时间
                if timestamp > 1e12:  # 毫秒级时间戳通常大于1e12
                    timestamp = timestamp / 1000
                return datetime.datetime.fromtimestamp(timestamp).strftime('%Y-%m-%d %H:%M:%S')
            else:
                # 如果已经是日期格式，直接返回
                return timestamp
        return None
    except Exception as e:
        print(f"查询错误: {e}")
        return None

# 获取各模块最新时间
def get_module_latest_times(tenant_id):
    conn = MysqlPool.get_conn(tenant_id)
    try:
        # 采购最新时间
        purchase_sql = f"SELECT MAX(create_time) as latest_time FROM purchase_order_info"
        purchase_time = get_latest_time(conn, purchase_sql, 'latest_time')

        # 销售最新时间
        sale_sql = f"SELECT MAX(create_time) as latest_time FROM sale_order"
        sale_time = get_latest_time(conn, sale_sql, 'latest_time')

        # 生产最新时间 (从两个表中取较晚的时间)
        product_sql = f"SELECT MAX(create_time) as latest_time FROM base_product"
        product_time = get_latest_time(conn, product_sql, 'latest_time')

        task_sql = f"SELECT MAX(create_time) as latest_time FROM sale_production_task"
        task_time = get_latest_time(conn, task_sql, 'latest_time')
        production_time = max(product_time, task_time) if product_time and task_time else (product_time or task_time)

        # 仓储入库最新时间
        inbound_sql = f"SELECT MAX(create_time) as latest_time FROM wms_inbound_form"
        inbound_time = get_latest_time(conn, inbound_sql, 'latest_time')

        # 仓储出库最新时间
        outbound_sql = f"SELECT MAX(create_time) as latest_time FROM wms_outbound_form"
        outbound_time = get_latest_time(conn, outbound_sql, 'latest_time')

        # 数采设备实时数据最晚时间
        device_sql = f"SELECT MAX(last_update_time) as latest_time FROM mdc_device_variate"
        device_time = get_latest_time(conn, device_sql, 'latest_time')

        return {
            'purchase': purchase_time,
            'sale': sale_time,
            'production': production_time,
            'inbound': inbound_time,
            'outbound': outbound_time,
            'device': device_time
        }
    finally:
        conn.close()

# 生成Excel报告
def generate_excel(ids):

    # 创建工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "企业业务数据时间"

    # 写入表头
    headers = ['地市', '区县', '企业名称', '采购最新时间', '销售最新时间', '生产最新时间', '仓储入库最新时间','仓储出库最新时间', '数采设备最新时间']
    for col_idx, header in enumerate(headers, 1):
        ws.cell(row=1, column=col_idx, value=header).font = Font(bold=True)

    # 设置表头列宽
    column_widths = [12, 12, 25, 20, 20, 20, 20, 20, 20]  # 根据需要调整各列宽度
    for col_idx, width in enumerate(column_widths, 1):
        col_letter = get_column_letter(col_idx)
        ws.column_dimensions[col_letter].width = width

    # 当前时间，用于判断是否超过3天
    current_time = datetime.datetime.now()
    three_days_ago = current_time - datetime.timedelta(days=3)
    red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')

    # 通过租户表获取所有租户ID，以及对应的地市、区县和企业名称
    conn = MysqlPool.get_tenant_conn()
    if not ids:
        return []
    try:
        tenant_sql = f"""
            SELECT id, addr_city_id, addr_county_id, name 
            FROM platform_tenant_info
            WHERE id IN ({','.join(map(str, ids))})
        """
        results = conn.get_all(tenant_sql)
        company_info = {}
        for result in results:
            id = result['id']
            city = result['addr_city_id']
            district = result['addr_county_id']
            company_name = result['name']
            company_info[id] = [city, district, company_name]
    finally:
        conn.close()

    # 遍历企业信息
    row = 2
    for tenant_id, info in company_info.items():
        city, district, company_name = info
        module_times = get_module_latest_times(tenant_id)

        # 填充企业基本信息
        ws.cell(row=row, column=1, value=city)
        ws.cell(row=row, column=2, value=district)
        ws.cell(row=row, column=3, value=company_name)

        # 填充各模块时间并检查是否超过3天
        col = 4
        for module, latest_time in module_times.items():
            cell = ws.cell(row=row, column=col, value=latest_time)
            # 检查是否超过3天
            if latest_time:
                try:
                    # 尝试将latest_time转换为datetime对象
                    if isinstance(latest_time, str):
                        # 假设日期格式为'%Y-%m-%d %H:%M:%S'，与我们之前格式化的一致
                        latest_time_dt = datetime.datetime.strptime(latest_time, '%Y-%m-%d %H:%M:%S')
                    else:
                        latest_time_dt = latest_time

                    if latest_time_dt < three_days_ago:
                        cell.fill = red_fill
                except ValueError:
                    # 处理日期解析错误
                    print(f"无法解析日期: {latest_time}")
            col += 1

        row += 1

    # 保存Excel文件
    base_dir = os.path.dirname(os.path.abspath(__file__))
    base_name = '企业业务数据时间'
    extension = '.xlsx'
    counter = 1

    # 获取当前日期（格式化为年月日）
    current_datetime = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")

    # 检查文件是否存在，如果存在则增加序号
    excel_path = os.path.join(base_dir, f'{base_name}_{current_datetime}{extension}')
    while os.path.exists(excel_path):
        excel_path = os.path.join(base_dir, f'{base_name}_{current_datetime}_{counter}{extension}')
        counter += 1

    wb.save(excel_path)
    print(f"Excel报告已生成: {excel_path}")

# 定时任务
def job():
    print(f"开始生成Excel报告: {datetime.datetime.now()}")
    target_ids = [159,327,354,355,238,245,248,246,247,329,330,331,332,249,250,263,264,265,349,326,328]
    generate_excel(target_ids)
    print(f"Excel报告生成完成: {datetime.datetime.now()}")

# 设置定时任务，每天上午9点执行
schedule.every().day.at("09:00").do(job)

# 测试运行
if __name__ == '__main__':
    print("脚本已启动，将每天上午9点生成Excel报告...")
    # 立即运行一次用于测试
    job()
    # 持续运行以保持定时任务活跃
    # while True:
    #     schedule.run_pending()
    #     time.sleep(1)